﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using Kingdee.BOS;

namespace KingdeeWebServiceProject.Data
{
    internal class SqlDatabase : AbstractDatabase
    {
        private string _ConnString;

        internal SqlDatabase(string strValue)
        {
            this._ConnString = strValue;
        }

        internal string ConnString
        {
            set { this._ConnString = value; }
            get { return this._ConnString; }
        }


        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <return>返回影响记录行</return>
        internal override int Execute(string strSql)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = conn;
                        cmd.Transaction = trans;//指定该事务
                        cmd.CommandText = strSql;
                        int rows = cmd.ExecuteNonQuery();
                        trans.Commit();
                        return rows;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        conn.Close();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句（带参数）
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="lstSqlParams">SQL参数列表</param>
        /// <return>返回影响记录行</return>
        internal override int Execute(string strSql, List<SqlParam> lstSqlParams)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = conn;
                        cmd.Transaction = trans;//指定该事务
                        cmd.CommandText = strSql;
                        if (lstSqlParams != null)
                        {
                            cmd.Parameters.AddRange(this.ConvertListSqlSqlParam(lstSqlParams).ToArray());
                        }
                        int rows = cmd.ExecuteNonQuery();
                        trans.Commit();
                        return rows;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        conn.Close();
                        throw ex;
                    }
                }
            }
        }


        /// <summary>
        /// 批量执行SQL语句
        /// </summary>
        /// <param name="lstSqlString">SQL语句列表</param>
        /// <return>返回影响记录行</return>
        internal override int ExecuteBatch(List<String> lstSql)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        int count = 0;
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = conn;
                        cmd.Transaction = trans;//指定该事务
                        foreach (var strSql in lstSql)
                        {
                            if (strSql.Trim().Length > 1)
                            {
                                cmd.CommandText = strSql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }

                        trans.Commit();
                        return count;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        conn.Close();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 批量执行SQL语句
        /// </summary>
        /// <param name="lstSqlObject">SQL语句列表</param>
        /// <return>返回影响记录行</return>
        internal override int ExecuteBatch(List<SqlObject> lstSqlObject)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        int count = 0;
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = conn;
                        cmd.Transaction = trans;//指定该事务
                        foreach (var objSql in lstSqlObject)
                        {
                            if (objSql != null)
                            {
                                cmd.CommandText = objSql.Sql;
                                if (objSql.SqlParams != null)
                                {
                                    cmd.Parameters.AddRange(this.ConvertListSqlSqlParam(objSql.SqlParams).ToArray());
                                }
                                count += cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                        }

                        trans.Commit();
                        return count;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        conn.Close();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <return>返回数据集DataSet</return>
        internal override DataSet ExecuteDateSet(string strSql)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnString))
            {

                conn.Open();
                DataSet ds = new DataSet();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = strSql;
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds, "ds");
                        return ds;
                    }
                }

            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="lstSqlParams">SQL参数列表</param>
        /// <return>返回数据集DataSet</return>
        internal override DataSet ExecuteDateSet(string strSql, List<SqlParam> lstSqlParams)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnString))
            {
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = strSql;
                    if (lstSqlParams != null)
                    {
                        cmd.Parameters.AddRange(this.ConvertListSqlSqlParam(lstSqlParams).ToArray());
                    }
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds, "ds");
                    return ds;
                }
                catch (SqlException ex)
                {
                    conn.Close();
                    throw ex;
                }
            }
        }


        #region  私有方法

        private SqlParameter ConvertSqlSqlParam(SqlParam KDSqlParam)
        {
            if (KDSqlParam != null)
            {
                SqlParameter paramSql = new SqlParameter(KDSqlParam.Name, KDSqlParam.Value);

                switch (KDSqlParam.CustemDbType)
                {
                    case KDDbType.RefCursor:
                        paramSql.DbType = DbType.Object;
                        break;
                    default:
                        paramSql.DbType = (DbType)KDSqlParam.CustemDbType;
                        break;
                }

                paramSql.Size = KDSqlParam.Size;
                paramSql.Direction = KDSqlParam.Direction;

                return paramSql;
            }

            return null;
        }

        private List<SqlParameter> ConvertListSqlSqlParam(List<SqlParam> lstSqlParams)
        {
            List<SqlParameter> lstSqlParameter = new List<SqlParameter>();

            if (lstSqlParams != null && lstSqlParams.Count > 0)
            {
                foreach (var KDSqlParam in lstSqlParams)
                {
                    lstSqlParameter.Add(this.ConvertSqlSqlParam(KDSqlParam));
                }
            }

            return lstSqlParameter;
        }

        #endregion
    }
}
